package sprinklerbee.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import sprinklerbee.db.DbConn;
import sprinklerbee.model.Group;
import sprinklerbee.model.Scheduler;
import sprinklerbee.model.SchedulerHistory;
import sprinklerbee.model.Sprinkler;
import sprinklerbee.utils.CommonUtils;

public class SchedulerDAOImpl extends DbConn implements SchedulerDAO {

	@Override
	public boolean addScheduler(Scheduler scheduler) throws RuntimeException {
		// TODO Auto-generated method stub
		try {
			String sql = "INSERT INTO `scheduler` VALUES(NULL,"
					+ scheduler.getSprinkler().getId() + ",'"
					+ scheduler.getStarttime() + "','" + scheduler.getEndtime()
					+ "','" + scheduler.getDescription() + "','"
					+ "Active" + "',NOW()," + scheduler.isMon() + ","
					+ scheduler.isTue() + "," + scheduler.isWed() + ","
					+ scheduler.isThur() + "," + scheduler.isFri() + ","
					+ scheduler.isSat() + "," + scheduler.isSun() + ")";
			super.update(sql);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
		
	}
	
	@Override
	public boolean addSchedulers(List<Scheduler> schedulers) throws RuntimeException {
		// TODO Auto-generated method stub
		try {
			List<String> sqls=new ArrayList<String>();
			for (Scheduler scheduler : schedulers) {
				sqls.add("INSERT INTO `scheduler` VALUES(NULL,"
						+ scheduler.getSprinkler().getId() + ",'"
						+ scheduler.getStarttime() + "','" + scheduler.getEndtime()
						+ "','" + scheduler.getDescription() + "','"
						+ "Active" + "',NOW()," + scheduler.isMon() + ","
						+ scheduler.isTue() + "," + scheduler.isWed() + ","
						+ scheduler.isThur() + "," + scheduler.isFri() + ","
						+ scheduler.isSat() + "," + scheduler.isSun() + ")");
			}
			super.executeTransaction(sqls);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
		
	}

	@Override
	public boolean delelteScheduler(Scheduler scheduler) throws RuntimeException {
		// TODO Auto-generated method stub
		try {
			String sql = "DELETE FROM `scheduler` WHERE id=" + scheduler.getId();
			super.update(sql);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
		
	}

	@Override
	public boolean updateSchedulerBySprinkler(Scheduler scheduler) {
		// TODO Auto-generated method stub
		try {
			String sql = "UPDATE `scheduler` SET status='"
					+ scheduler.getStatus() + "' WHERE sprinkler_id =" + scheduler.getSprinkler().getId();
			super.update(sql);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean updateSchedulersByGroup(Group group, Scheduler scheduler) {
		// TODO Auto-generated method stub
		try {
			String sql = "UPDATE `scheduler` SET  status='"
					+ scheduler.getStatus()
					+ "' WHERE sprinkler_id IN ( SELECT s.id FROM `sprinkler` s LEFT OUTER JOIN `group` g on s.group_id=g.id WHERE g.name='"
					+ group.getName() + "')";
			super.update(sql);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
		
	}

	@Override
	public Scheduler querySchedulerById(int id) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM `scheduler` WHERE id=" + id;
		Scheduler scheduler = null;
		ResultSet rs = super.query(sql);
		try {
			if (rs.next()) {
				scheduler = new Scheduler();
				scheduler.setId(rs.getInt("id"));
				Sprinkler sprinkler = new Sprinkler();
				sprinkler.setId(rs.getInt("sprinkler_id"));
				scheduler.setStarttime(rs.getString("starttime"));
				scheduler.setEndtime(rs.getString("endtime"));
				scheduler.setDescription(rs.getString("description"));
				scheduler.setCreatetime(rs.getDate("createtime"));
				scheduler.setStatus(rs.getString("status"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scheduler;
	}

	@Override
	public List<Scheduler> querySchedulersBySprinkler(String sprinklerName) {
		// TODO Auto-generated method stub
		String sql = "SELECT sc.*,s.name AS sprinkler_name FROM `scheduler` AS sc LEFT OUTER JOIN `sprinkler` AS s on sc.sprinkler_id=s.id WHERE sc.status='Active' AND s.status='Enabled' AND s.name='"+sprinklerName+"'";
		List<Scheduler> list = null;
		ResultSet rs = super.query(sql);
		try {
			list = new ArrayList<Scheduler>();
			while (rs.next()) {
				Scheduler scheduler = new Scheduler();
				scheduler.setId(rs.getInt("id"));
				Sprinkler sprinkler = new Sprinkler();
				sprinkler.setId(rs.getInt("sprinkler_id"));
				sprinkler.setName(rs.getString("sprinkler_name"));
				scheduler.setSprinkler(sprinkler);
				scheduler.setStarttime(rs.getString("starttime"));
				scheduler.setEndtime(rs.getString("endtime"));
				scheduler.setDescription(rs.getString("description"));
				scheduler.setCreatetime(rs.getDate("createtime"));
				scheduler.setStatus(rs.getString("status"));
				scheduler.setMon(rs.getBoolean("Mon"));
				scheduler.setTue(rs.getBoolean("Tue"));
				scheduler.setWed(rs.getBoolean("Wed"));
				scheduler.setThur(rs.getBoolean("Thur"));
				scheduler.setFri(rs.getBoolean("Fri"));
				scheduler.setSat(rs.getBoolean("Sat"));
				scheduler.setSun(rs.getBoolean("Sun"));
				list.add(scheduler);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Scheduler> querySchedulersByGroup(String groupName) {
		// TODO Auto-generated method stub
		String sql = "SELECT sc.*,s.name AS sprinkler_name FROM `scheduler` AS sc LEFT OUTER JOIN `sprinkler` AS s ON sc.sprinkler_id=s.id WHERE sc.status='Active' AND s.status='Enabled' AND s.group_id in ( SELECT ID FROM `group` WHERE name='"+groupName+"')";
		List<Scheduler> list = null;
		ResultSet rs = super.query(sql);
		try {
			list = new ArrayList<Scheduler>();
			while (rs.next()) {
				Scheduler scheduler = new Scheduler();
				scheduler.setId(rs.getInt("id"));
				Sprinkler sprinkler = new Sprinkler();
				sprinkler.setId(rs.getInt("sprinkler_id"));
				sprinkler.setName(rs.getString("sprinkler_name"));
				scheduler.setSprinkler(sprinkler);
				scheduler.setStarttime(rs.getString("starttime"));
				scheduler.setEndtime(rs.getString("endtime"));
				scheduler.setDescription(rs.getString("description"));
				scheduler.setCreatetime(rs.getDate("createtime"));
				scheduler.setStatus(rs.getString("status"));
				scheduler.setMon(rs.getBoolean("Mon"));
				scheduler.setTue(rs.getBoolean("Tue"));
				scheduler.setWed(rs.getBoolean("Wed"));
				scheduler.setThur(rs.getBoolean("Thur"));
				scheduler.setFri(rs.getBoolean("Fri"));
				scheduler.setSat(rs.getBoolean("Sat"));
				scheduler.setSun(rs.getBoolean("Sun"));
				list.add(scheduler);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	@Override
	public List<SchedulerHistory> querySchedulerHistoryBySprinklerId(
			int sprinklerId) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM `scheduler_history` WHERE sprinkler_id="
				+ sprinklerId;
		List<SchedulerHistory> list = null;
		ResultSet rs = super.query(sql);
		try {
			list = new ArrayList<SchedulerHistory>();
			while (rs.next()) {
				SchedulerHistory schedulerHistory = new SchedulerHistory();
				schedulerHistory.setId(rs.getInt("id"));
				Sprinkler sprinkler = new Sprinkler();
				sprinkler.setId(rs.getInt("sprinkler_id"));
				schedulerHistory.setSprinkler(sprinkler);
				Scheduler scheduler = new Scheduler();
				scheduler.setId(rs.getInt("scheduler_id"));
				schedulerHistory.setScheduler(scheduler);
				schedulerHistory.setStarttime(rs.getDate("starttime"));
				schedulerHistory.setEndtime(rs.getDate("endtime"));
				schedulerHistory.setConsumption(rs.getDouble("consumption"));
				schedulerHistory.setDuration(rs.getDouble("duration"));
				list.add(schedulerHistory);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Scheduler> queryAllSchedulers() {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM `scheduler` AS sc LEFT OUTER JOIN `sprinkler` AS s ON sc.sprinkler_id=s.id WHERE sc.status='Active' AND s.status='Enabled'";
		List<Scheduler> list = null;
		ResultSet rs = super.query(sql);
		try {
			list = new ArrayList<Scheduler>();
			while (rs.next()) {
				Scheduler scheduler = new Scheduler();
				scheduler.setId(rs.getInt("id"));
				Sprinkler sprinkler = new Sprinkler();
				sprinkler.setId(rs.getInt("sprinkler_id"));
				sprinkler.setName(rs.getString("name"));
				scheduler.setSprinkler(sprinkler);
				scheduler.setStarttime(rs.getString("starttime"));
				scheduler.setEndtime(rs.getString("endtime"));
				scheduler.setDescription(rs.getString("description"));
				scheduler.setCreatetime(rs.getDate("createtime"));
				scheduler.setStatus(rs.getString("status"));
				scheduler.setMon(rs.getBoolean("Mon"));
				scheduler.setTue(rs.getBoolean("Tue"));
				scheduler.setWed(rs.getBoolean("Wed"));
				scheduler.setThur(rs.getBoolean("Thur"));
				scheduler.setFri(rs.getBoolean("Fri"));
				scheduler.setSat(rs.getBoolean("Sat"));
				scheduler.setSun(rs.getBoolean("Sun"));
				list.add(scheduler);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Scheduler> querySchedulersRealTime() {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM `scheduler` AS sc LEFT OUTER JOIN `sprinkler` AS s ON sc.sprinkler_id=s.id WHERE sc.status='Active' AND s.status='Enabled' AND TIME_FORMAT(sc.starttime,'%h:%i' ) < TIME_FORMAT(NOW(),'%h:%i' ) AND TIME_FORMAT(sc.endtime,'%h:%i' ) > TIME_FORMAT(NOW(),'%h:%i' )";
		List<Scheduler> list = null;
		ResultSet rs = super.query(sql);
		try {
			list = new ArrayList<Scheduler>();
			while (rs.next()) {
				Scheduler scheduler = new Scheduler();
				scheduler.setId(rs.getInt("id"));
				Sprinkler sprinkler = new Sprinkler();
				sprinkler.setId(rs.getInt("sprinkler_id"));
				sprinkler.setName(rs.getString("name"));
				scheduler.setSprinkler(sprinkler);
				scheduler.setStarttime(rs.getString("starttime"));
				scheduler.setEndtime(rs.getString("endtime"));
				scheduler.setDescription(rs.getString("description"));
				scheduler.setCreatetime(rs.getDate("createtime"));
				scheduler.setStatus(rs.getString("status"));
				scheduler.setMon(rs.getBoolean("Mon"));
				scheduler.setTue(rs.getBoolean("Tue"));
				scheduler.setWed(rs.getBoolean("Wed"));
				scheduler.setThur(rs.getBoolean("Thur"));
				scheduler.setFri(rs.getBoolean("Fri"));
				scheduler.setSat(rs.getBoolean("Sat"));
				scheduler.setSun(rs.getBoolean("Sun"));
				list.add(scheduler);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean addSchedulerConsumption(List<SchedulerHistory> list)
			throws RuntimeException {
		// TODO Auto-generated method stub
		try {
			List<String> sqls=new ArrayList<String>();
			SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			for (SchedulerHistory schedulerHistory : list) {
				String sql = "INSERT INTO `scheduler_history` VALUES(NULL,"
						+ schedulerHistory.getScheduler().getId() + ",'"
						+ format.format(schedulerHistory.getStarttime()) + "','" + format.format(schedulerHistory.getEndtime())
						+ "',"+schedulerHistory.getDuration()+","
						+schedulerHistory.getConsumption() +"," + schedulerHistory.getSprinkler().getId() + ")";
				sqls.add(sql);
			}
			return super.executeTransaction(sqls);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public double queryMonthlyConsumption(String month) {
		// TODO Auto-generated method stub
		String sql = "select sum(consumption) total from `scheduler_history` where DATE_FORMAT(endtime,'%c')='"
				+ month + "'";
		ResultSet rs = super.query(sql);
		double total = 0;
		try {
			if (rs.next()) {
				total = rs.getDouble("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public List<SchedulerHistory> querySprinklerConsumption() {
		// TODO Auto-generated method stub
		String sql = "SELECT name, sum(consumption) AS total FROM `scheduler_history` AS sh LEFT OUTER JOIN `sprinkler` AS s on sh.sprinkler_id=s.id GROUP BY sprinkler_id";
		List<SchedulerHistory> list = null;
		ResultSet rs = super.query(sql);
		try {
			list = new ArrayList<SchedulerHistory>();
			while (rs.next()) {
				SchedulerHistory history=new SchedulerHistory();
				history.setSprinkler(new Sprinkler(rs.getString("name")));
				history.setConsumption(rs.getDouble("total"));
				list.add(history);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<SchedulerHistory> queryGroupConsumption(String groupName) {
		// TODO Auto-generated method stub
		String sql = "SELECT s.name AS name, sum(consumption) AS total FROM `scheduler_history` AS sh LEFT OUTER JOIN `sprinkler` AS s on sh.sprinkler_id=s.id LEFT OUTER JOIN `group` AS g ON s.group_id=g.id WHERE g.name='"+groupName+"' GROUP BY s.id";
		List<SchedulerHistory> list = null;
		ResultSet rs = super.query(sql);
		try {
			list = new ArrayList<SchedulerHistory>();
			while (rs.next()) {
				SchedulerHistory history=new SchedulerHistory();
				history.setSprinkler(new Sprinkler(rs.getString("name")));
				history.setConsumption(rs.getDouble("total"));
				list.add(history);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<SchedulerHistory> queryGroupConsumption() {
		// TODO Auto-generated method stub
		String sql = "SELECT g.name AS name, sum(consumption) AS total FROM `scheduler_history` AS sh LEFT OUTER JOIN `sprinkler` AS s on sh.sprinkler_id=s.id LEFT OUTER JOIN `group` AS g ON s.group_id=g.id GROUP BY g.id";
		List<SchedulerHistory> list = null;
		ResultSet rs = super.query(sql);
		try {
			list = new ArrayList<SchedulerHistory>();
			while (rs.next()) {
				SchedulerHistory history=new SchedulerHistory();
				history.setSprinkler(new Sprinkler(rs.getString("name")));
				history.setConsumption(rs.getDouble("total"));
				list.add(history);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean updateAllSchedulers(Scheduler scheduler) throws RuntimeException {
		// TODO Auto-generated method stub
		try {
			String sql = "UPDATE `scheduler` SET status='"
					+ scheduler.getStatus() + "'";
			super.update(sql);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public List<Scheduler> querySchedulersForToday() {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM `scheduler` AS sc LEFT OUTER JOIN `sprinkler` AS s ON sc.sprinkler_id=s.id WHERE sc.status='Active' AND s.status='Enabled' AND sc."+CommonUtils.getWeekDay(Calendar.getInstance())+"=1";
		List<Scheduler> list = null;
		ResultSet rs = super.query(sql);
		try {
			list = new ArrayList<Scheduler>();
			while (rs.next()) {
				Scheduler scheduler = new Scheduler();
				scheduler.setId(rs.getInt("id"));
				Sprinkler sprinkler = new Sprinkler();
				sprinkler.setId(rs.getInt("sprinkler_id"));
				sprinkler.setName(rs.getString("name"));
				scheduler.setSprinkler(sprinkler);
				scheduler.setStarttime(rs.getString("starttime"));
				scheduler.setEndtime(rs.getString("endtime"));
				scheduler.setDescription(rs.getString("description"));
				scheduler.setCreatetime(rs.getDate("createtime"));
				scheduler.setStatus(rs.getString("status"));
				scheduler.setMon(rs.getBoolean("Mon"));
				scheduler.setTue(rs.getBoolean("Tue"));
				scheduler.setWed(rs.getBoolean("Wed"));
				scheduler.setThur(rs.getBoolean("Thur"));
				scheduler.setFri(rs.getBoolean("Fri"));
				scheduler.setSat(rs.getBoolean("Sat"));
				scheduler.setSun(rs.getBoolean("Sun"));
				list.add(scheduler);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

}
